﻿using log4net;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace SQLDataUploadHelper.commons.utils
{
    public class SqliteDbUtils
    {
        private ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
        private static volatile SqliteDbUtils instance;
        private static object syncRoot = new Object();
        private static object connectLock = new Object();
        private static readonly ReaderWriterLockSlim readerWriterLockSlim = new ReaderWriterLockSlim();
        private int timeOut = 5000;//数据库等待时间
        private string databasePath = "";
        private SQLiteConnection sqliteConnection = null;

        /// <summary>
        /// 单例
        /// </summary>
        public static SqliteDbUtils Instance
        {
            get
            {
                if (instance == null)
                {
                    lock (syncRoot)
                    {
                        if (instance == null)
                        {
                            instance = new SqliteDbUtils();
                        }
                    }
                }
                return instance;
            }
        }

        /// <summary>
        /// 数据库路径
        /// </summary>
        public string DatabasePath
        {
            get { return databasePath; }
            set { databasePath = value; }
        }

        /// <summary>
        /// 执行查询语句，返回查询结果
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dbConn"></param>
        /// <param name="dbComm"></param>
        /// <returns></returns>
        public SQLiteDataReader runSelectSql(string sql)
        {
            SQLiteDataReader sqliteDataReader = null;
            try
            {
                if (!isConnected() && !connect())
                {
                    log.Info(sql);
                    log.Error("数据库文件打开失败");
                    return null;
                }
                SQLiteCommand sqliteCommand = new SQLiteCommand();
                sqliteCommand.Connection = sqliteConnection;
                sqliteCommand.CommandType = CommandType.Text;
                sqliteCommand.CommandText = sql;
                sqliteDataReader = sqliteCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                log.Info(sql);
                log.Error(ex.ToString());
            }
            return sqliteDataReader;
        }

        /// <summary>
        /// 执行插入语句，供调用
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="strPath"></param>
        /// <returns></returns>
        public int execInsertSql(string sql)
        {
            int newId = -1;
            try
            {
                if (!isConnected() && !connect())
                {
                    log.Info(sql);
                    log.Error("数据库文件打开失败");
                    return newId;
                }
                SQLiteCommand sqliteCommand = new SQLiteCommand();
                sqliteCommand.Connection = sqliteConnection;
                sqliteCommand.CommandType = CommandType.Text;
                SQLiteTransaction dbTrans = sqliteCommand.Connection.BeginTransaction();
                sqliteCommand.Transaction = dbTrans;
                sqliteCommand.CommandText = sql;
                sqliteCommand.ExecuteNonQuery();
                sqliteCommand.Transaction.Commit();
                sqliteCommand.CommandText = "select last_insert_rowid()";
                newId = Convert.ToInt32(sqliteCommand.ExecuteScalar());
            }
            catch (Exception ex)
            {
                log.Info(sql);
                log.Error(ex.ToString());
                newId = -1;
            }
            return newId;
        }

        /// <summary>
        /// 执行删除语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public bool execDeleteSql(string sql)
        {
            bool result = false;
            try
            {
                if (!isConnected() && !connect())
                {
                    log.Info(sql);
                    log.Error("数据库文件打开失败");
                    return result;
                }
                SQLiteCommand sqliteCommand = new SQLiteCommand();
                sqliteCommand.Connection = sqliteConnection;
                sqliteCommand.CommandType = CommandType.Text;
                SQLiteTransaction dbTrans = sqliteCommand.Connection.BeginTransaction();
                sqliteCommand.Transaction = dbTrans;
                sqliteCommand.CommandText = sql;
                sqliteCommand.ExecuteNonQuery();
                sqliteCommand.Transaction.Commit();
                result = true;
            }
            catch (Exception ex)
            {
                log.Info(sql);
                log.Error(ex.ToString());
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 更新信息
        /// </summary>
        /// <param name="strSql">数据库语句</param>
        /// <returns></returns>
        public bool execUpdateSql(string sql)
        {
            bool result = false;
            try
            {
                if (!isConnected() && !connect())
                {
                    log.Info(sql);
                    log.Error("数据库文件打开失败");
                    return result;
                }
                SQLiteCommand sqliteCommand = new SQLiteCommand();
                sqliteCommand.Connection = sqliteConnection;
                sqliteCommand.CommandType = CommandType.Text;
                SQLiteTransaction dbTrans = sqliteCommand.Connection.BeginTransaction();
                sqliteCommand.Transaction = dbTrans;
                sqliteCommand.CommandText = sql;
                sqliteCommand.ExecuteNonQuery();
                sqliteCommand.Transaction.Commit();
                result = true;
            }
            catch (Exception ex)
            {
                log.Info(sql);
                log.Error(ex.ToString());
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        private SqliteDbUtils() { }
        /// <summary>
        /// 数据库连接
        /// </summary>
        /// <returns></returns>
        private bool connect()
        {
            lock (connectLock)
            {
                if (sqliteConnection == null)
                {
                    sqliteConnection = new SQLiteConnection(databasePath);
                }
                if (sqliteConnection.State != ConnectionState.Open)
                {
                    sqliteConnection.Open();
                }
            }
            return sqliteConnection.State == ConnectionState.Open;
        }

        /// <summary>
        /// 判断是否是连接状态
        /// </summary>
        /// <returns></returns>
        private bool isConnected()
        {
            if (sqliteConnection == null)
            {
                return false;
            }
            return sqliteConnection.State == ConnectionState.Open;
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        private void disConnect()
        {
            if (sqliteConnection == null)
            {
                return;
            }
            if (sqliteConnection.State == ConnectionState.Open)
            {
                sqliteConnection.Close();
            }
        }
    }
}
